USE [JaxDug]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetPresentationsByTagID') AND TYPE IN (N'P', N'PC'))
BEGIN
	DROP PROC GetPresentationsByTagID
	PRINT 'DROP PROC GetPresentationsByTagID'
END
GO

-- ===============================================================
-- Author:		RbH
-- Create date: 2012.03.21
-- Description:	Selects basic info about Presentations by TagID
-- Ussage:		EXEC GetPresentationsByTagID 1
-- ===============================================================
CREATE PROCEDURE dbo.GetPresentationsByTagID
	@TagID INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @PresentationIDs TABLE ( PresentationID INT )
	
	INSERT INTO @PresentationIDs ( PresentationID )
	SELECT pt.PresentationID
	FROM dbo.PresentationTags pt
	WHERE pt.TagID = @TagID
	
    SELECT p.PresentationID, p.Title, p.StartDateTime, p.EndDateTime,
		u.UserID AS 'SpeakerID', u.FirstName, u.LastName
	FROM dbo.Presentations p
		INNER JOIN @PresentationIDs tv ON p.PresentationID = tv.PresentationID
		INNER JOIN dbo.Users u ON u.UserID = p.SpeakerID
	ORDER BY p.PresentationID ASC
	
	SELECT DISTINCT tv.PresentationID, t.TagID, t.Tag
	FROM @PresentationIDs tv
		INNER JOIN dbo.PresentationTags pt ON tv.PresentationID = pt.PresentationID
		INNER JOIN dbo.Tags t ON pt.TagID = t.TagID
END
GO

PRINT 'CREATE PROC GetPresentationsByTagID'

GRANT EXEC ON GetPresentationsByTagID TO PublicDbUser
GO

PRINT 'GRANT EXEC ON GetPresentationsByTagID TO PublicDbUser'